package com.eling.elcms.travel.dao.hibernate;

import com.eling.elcms.basedoc.model.PersonalInfo;
import com.eling.elcms.core.dao.hibernate.GenericDaoHibernate;
import com.eling.elcms.member.model.Member;
import com.eling.elcms.travel.dao.ICheckInImplementDao;
import com.eling.elcms.travel.model.*;
import com.eling.elcms.travel.model.view.ChangeRoomSettlementView;
import com.eling.elcms.travel.model.view.CheckInDetailView;
import org.springframework.stereotype.Repository;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;


@Repository
public class CheckInImplementDaoHibernate  extends GenericDaoHibernate<CheckInImplement, Long> implements ICheckInImplementDao{

	public CheckInImplementDaoHibernate() {
		super(CheckInImplement.class);
	}

	@SuppressWarnings("unchecked")
	@Override
	public CheckInDetailView queryAll(CheckInImplement checkInImplement,Long pkRoom) {
		StringBuffer sql = new StringBuffer();
		sql.append("  	SELECT	");
		sql.append("  		pi.pkPersonalInfo as ppe,pi.idNumber as pid,pi.`name` as pname,pi.sex, ");
		sql.append("  		pi.mobilePhone,roomp.checkIn,tra.memberCertificates,pis.pkPersonalInfo as psp,pis.idNumber as psid,pis.`name` psname,pt.`name`,pt.pkProduct, ");
		sql.append("		roomr.waterElectric,roomr.waterMeter,roomr.electricMeter,roomr.breakfast,roomr.price,sin.member,roomr.pkRoomRegister,");
		sql.append("        roomr.version,roomp.pkRoomPersonalInfo,roomp.version as rv,roomr.checkInPrice,roomr.checkInPriceMark,roomr.checkInType,roomr.roomStatus	");
		sql.append("  	FROM	");
		sql.append("  		travel_checkinimplement cii	");
		sql.append("  	LEFT JOIN travel_roompersonalInfo roomp ON roomp.pkCIImplement = cii.pkCIImplement	");
		sql.append("  	LEFT JOIN travel_roomregister roomr ON roomr.pkRoomRegister = roomp.pkRoomRegister	");
		sql.append("  	LEFT JOIN travel_signupregister sin ON roomp.pkSignUpRegister = sin.pkSignUpRegister	");
		sql.append("  	LEFT JOIN bd_personalinfo pi ON sin.pkPersonalInfo=pi.pkPersonalInfo	");
		sql.append("  	LEFT JOIN travel_travelcontract tra ON tra.pkTravelContract=sin.pkTravelContract	");
		sql.append("  	LEFT JOIN mem_member mem ON tra.pkMember=mem.pkMember 	");
		sql.append("  	LEFT JOIN bd_personalinfo pis ON mem.pkPersonalInfo=pis.pkPersonalInfo	");
		sql.append("  	LEFT JOIN travel_productrule prod ON prod.pkProductRule=tra.pkProductRule	");
		sql.append("  	LEFT JOIN travel_product pt ON pt.pkProduct=prod.pkProduct	");
		sql.append("  WHERE 			");
		sql.append("  	cii.pkCIImplement="+checkInImplement.getPkCIImplement()+"	");
		sql.append("  and	roomr.pkRoom="+pkRoom+"	");
		List<Object[]> objList = getSession().createNativeQuery(sql.toString()).list();
		List<RoomPersonalInfo> list=new ArrayList<RoomPersonalInfo>();
		CheckInDetailView cidv=new CheckInDetailView();
		CheckInImplement c=new CheckInImplement();
		c.setPkCIImplement(checkInImplement.getPkCIImplement());
		for(Object[] o :objList){
			RoomPersonalInfo rpi=new RoomPersonalInfo();
			rpi.setPkRoomPersonalInfo(o[20]==null?null:Long.valueOf(o[20].toString()));
			rpi.setVersion(o[21]==null?null:Integer.valueOf(o[21].toString()));
			SignUpRegister su=new SignUpRegister();
			PersonalInfo p=new PersonalInfo();
			p.setPkPersonalInfo(o[0]==null?null:Long.valueOf(o[0].toString()));
			p.setIdNumber(o[1]==null?null:o[1].toString());
			p.setName(o[2]==null?null:o[2].toString());
			if(o[3].toString().equals(com.eling.elcms.basedoc.model.Sex.Female.name())){
				p.setSex(com.eling.elcms.basedoc.model.Sex.Female);
			}else{
				p.setSex(com.eling.elcms.basedoc.model.Sex.Male);
			}
			p.setMobilePhone(o[4]==null?null:o[4].toString());
			if(o[5]!=null&&o[5].toString().equals("true")){
				rpi.setCheckIn(true);
			}else{
				rpi.setCheckIn(false);
			}
			su.setPersonalInfo(p);
			TravelContract tc=new TravelContract();
			tc.setMemberCertificates(o[6]==null?null:o[6].toString());
			PersonalInfo pp=new PersonalInfo();
			Member mem=new Member();
			pp.setPkPersonalInfo(o[7]==null?null:Long.valueOf(o[7].toString()));
			pp.setIdNumber(o[8]==null?null:o[8].toString());
			pp.setName(o[9]==null?null:o[9].toString());
			ProductRule pr=new ProductRule();
			Product product=new Product();
			product.setPkProduct(o[11]==null?null:Long.valueOf(o[11].toString()));
			product.setName(o[10]==null?null:o[10].toString());
			pr.setProduct(product);
			tc.setProductRule(pr);
			mem.setPersonalInfo(pp);
			tc.setMember(mem);
			su.setTravelContract(tc);
			
			RoomRegister rr=new RoomRegister();
			if(o[12]!=null){
				if(o[12].toString().equals("true")){
					rr.setWaterElectric(true);
				}else{
					rr.setWaterElectric(false);
				}
			}
			rr.setWaterMeter(o[13]==null?null:Double.valueOf(o[13].toString()));
			rr.setElectricMeter(o[14]==null?null:Double.valueOf(o[14].toString()));
			if(o[15]!=null){
				if(o[15].toString().equals("true")){
					rr.setBreakfast(true);
				}else{
					rr.setBreakfast(false);
				}
			}
			rr.setPrice(o[16]==null?null:Double.valueOf(o[16].toString()));
			rr.setPkRoomRegister(o[18]==null?null:Long.valueOf(o[18].toString()));
			rr.setVersion(o[19]==null?null:Integer.valueOf(o[19].toString()));
			rr.setCheckInPrice(o[22]==null?null:Double.valueOf(o[22].toString()));
			rr.setCheckInPriceMark(o[23]==null?null:o[23].toString());
			if(o[25].toString().equals(RoomRegister.RoomStatus.NoCheckIn.name())){
				rr.setRoomStatus(RoomRegister.RoomStatus.NoCheckIn);
			}else if(o[25].toString().equals(RoomRegister.RoomStatus.CheckIn.name())){
				rr.setRoomStatus(RoomRegister.RoomStatus.CheckIn);
			}else{
				rr.setRoomStatus(RoomRegister.RoomStatus.finish);
			}
			if(o[24]!=null){
				if(o[24].toString().equals("Member")){
					rr.setCheckInType(RoomRegister.CheckInType.Member);
				}else if(o[24].toString().equals("Alliance")){
					rr.setCheckInType(RoomRegister.CheckInType.Alliance);
				}
			}
			if(o[17]!=null){
				if(o[17].toString().equals("true")){
					su.setMember(true);
				}else{
					su.setMember(false);
				}
			}
			rpi.setSignUpRegister(su);
			rpi.setRoomRegister(rr);
			list.add(rpi);
		}
		cidv.setRoompersonalInfo(list);
		return cidv;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ChangeRoomSettlementView> queryChangeRoom(Long pkGroupReserve) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT bp.idNumber,bp.name name1,CASE WHEN bp.sex='Male' THEN '男' ELSE '女' END,trt2.name name2,br2.`code` code1,   ");
		sql.append(" trt.`name` name3,br.`code` code2,tcr.changeRoomDate,bp.pkPersonalInfo ");
		sql.append(" FROM                                                      ");
		sql.append("  travel_groupreserve tgr                                  ");
		sql.append("   JOIN travel_checkinimplement tci                        ");
		sql.append("  ON tci.pkGroupReserve=tgr.pkGroupReserve                 ");
		sql.append("   JOIN travel_roompersonalInfo trp                        ");
		sql.append(" ON trp.pkCIImplement=tci.pkCIImplement               ");
		sql.append(" JOIN travel_roomregister trr ON trr.pkRoomRegister = trp.pkRoomRegister               ");
		sql.append(" JOIN bd_room br2 ON trr.pkRoom = br2.pkRoom               ");
		sql.append(" JOIN travel_roomtype trt2 ON trt2.pkRoomType = br2.pkRoomType              ");
		sql.append("   JOIN travel_signupregister tsr                          ");
		sql.append(" ON tsr.pkSignUpRegister=trp.pkSignUpRegister              ");
		sql.append("  JOIN bd_personalinfo bp                                  ");
		sql.append(" ON bp.pkPersonalInfo=tsr.pkPersonalInfo                   ");
		sql.append("  JOIN travel_changeroom tcr                               ");
		sql.append(" ON tcr.pkPersonalInfo=bp.pkPersonalInfo                   ");
		sql.append("  JOIN bd_room br                                          ");
		sql.append(" ON br.pkRoom=tcr.pkRoom                                   ");
		sql.append("  JOIN travel_roomtype trt                                 ");
		sql.append(" ON trt.pkRoomType=br.pkRoomType                           ");
		sql.append("  WHERE tgr.pkGroupReserve=:pkGroupReserve                 ");
		sql.append(" ORDER BY tcr.changeRoomDate                               ");
		
		List<Object[]> objList = getSession().createNativeQuery(sql.toString()).setParameter("pkGroupReserve", pkGroupReserve).list();
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		List<ChangeRoomSettlementView> viewList = new ArrayList<>();
		Long lastPk1=0l;
		Long lastPk2=0l;
		String lastRoomType1 = "";
		String lastRoomCode1 = "";
		String lastRoomType2 = "";
		String lastRoomCode2 = "";
		for (int i = 0; i < objList.size(); i++) {
			ChangeRoomSettlementView changeRoomSettlView = new ChangeRoomSettlementView();
			Object[] objects = objList.get(i);
			changeRoomSettlView.setIdNumber(objects[0]==null?"":objects[0].toString());
			changeRoomSettlView.setName(objects[1]==null?"":objects[1].toString());
			changeRoomSettlView.setSex(objects[2]==null?"":objects[2].toString());
			changeRoomSettlView.setRoomType(objects[3]==null?"":objects[3].toString());
			changeRoomSettlView.setRoomCode(objects[4]==null?"":objects[4].toString());
			changeRoomSettlView.setChangeRoomType(objects[5]==null?"":objects[5].toString());
			changeRoomSettlView.setChangeRoomCode(objects[6]==null?"":objects[6].toString());
			try {
				changeRoomSettlView.setChangeRoomDate(objects[7]==null?null:df.parse(objects[7].toString()));
			} catch (ParseException e) {
				e.printStackTrace();
			}
			if(i==0){
				lastPk1 = (objects[8]==null?0l:Long.parseLong(objects[8].toString()));
				lastRoomType1 = (objects[5]==null?"":objects[5].toString());
				lastRoomCode1 = (objects[6]==null?"":objects[6].toString());
			}else if(lastPk1!=Long.parseLong(objects[8].toString())&&lastPk2==0l){
				lastPk2 = (objects[8]==null?0l:Long.parseLong(objects[8].toString()));
				lastRoomType2 = (objects[5]==null?"":objects[5].toString());
				lastRoomCode2 = (objects[6]==null?"":objects[6].toString());
			}else if(lastPk1==Long.parseLong(objects[8].toString())){
				changeRoomSettlView.setRoomCode(lastRoomCode1);
				changeRoomSettlView.setRoomType(lastRoomType1);
				lastRoomType1 = (objects[5]==null?"":objects[5].toString());
				lastRoomCode1 = (objects[6]==null?"":objects[6].toString());
			}else {
				changeRoomSettlView.setRoomCode(lastRoomCode2);
				changeRoomSettlView.setRoomType(lastRoomType2);
				lastRoomType2 = (objects[5]==null?"":objects[5].toString());
				lastRoomCode2 = (objects[6]==null?"":objects[6].toString());
			}
			viewList.add(changeRoomSettlView);
		}
		return viewList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Long queryGroupReservePk(Long pkCIImplement) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT   ");
		sql.append("  tc.pkGroupReserve,   ");
		sql.append("  tc.pkCIImplement   ");
		sql.append("FROM   ");
		sql.append("	travel_checkinimplement tc   ");
		sql.append("WHERE   ");
		sql.append("	tc.pkCIImplement = "+pkCIImplement+"   ");
		List<Object[]> list = getSession().createNativeQuery(sql.toString()).list();
		Object[] objects = list.get(0);
		String str = objects[0].toString();
		long l = Long.parseLong(str);
		return l;
	}
	
}
